using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Reflection;
using System.Collections.ObjectModel;

namespace Sys.DataAccess
{
    //The internals of this class is not database independent.
    //But more importantly, the interface is.

    //TODO: Concurrency, when returning typed data.
    public abstract class DatabaseCommand
    {
        public delegate DatabaseCommand CreateCommand(string commandText, CommandType commandType, bool resultStructureIsDynamic);

        #region Fields
        protected string connectionString;
        protected string commandText;
        protected CommandType commandType;
        //If the number of tables, or the columns in the table varies based on query parameters, we should not be caching them.
        //resultStructureIsDynamic
        protected bool resultStructureIsDynamic;
        #endregion

        #region Constructor
        public DatabaseCommand(string commandText, CommandType commandType, bool resultStructureIsDynamic, string connectionString)
        {
            this.commandText = commandText;
            this.commandType = commandType;
            this.connectionString = connectionString;
            this.resultStructureIsDynamic = resultStructureIsDynamic;
        }
        #endregion

        #region Abstract Members
        public abstract void AddInParameter(string parameterName, object value);
        public abstract void AddInParameter(string parameterName, DbType paramType, object value);
        public abstract void AddOutputParameter(string parameterName, DbType dbType); 
        public abstract void AddOutputParameter(string parameterName, DbType dbType, int size);
        public abstract int ExecuteNonQuery();
        public abstract object ExecuteScalar();
        public abstract DataTable GetResultsAsTable();
        public abstract DataSet GetResultsAsDataSet();
        public abstract object GetParameterValue(string paramName);
        #endregion

        //This method does all the heavy-lifting.
        private CollectionT GetCollection_FromTable<T, CollectionT>(DataTable resultTable, int tableNum)
            where CollectionT : Collection<T>, new()
            where T : new()
        {
            Type collectionType = typeof(T);
            CollectionT results = new CollectionT();

            //Don't go through reflection, if we have nothing to return.
            if (resultTable.Rows.Count == 0) return results;
                        
            ObjectConstructionInfo objConstructInfo;    //Will help build the object from the DataTable
            //if the resultStructureIsDynamic is _FALSE_, we can cache.
            //This is likely to be the common case.
            if (!resultStructureIsDynamic)
            {
                //QueryResultMap caches object construction data for every connectionString + commandText combination
                //  Some queries might return multiple tables, in which case QueryResultMap will contain multiple
                //  ObjectConstructionInfo objects for that query.
                QueryResultMap map = QueryResultMap.Get(connectionString, commandText);

                //The Construction Info for the nth datatable in a dataset 
                //  should be in the nth ObjectConstructionInfo inside QueryResultMap 
                if (map.ContainsKey(tableNum))
                    objConstructInfo = map[tableNum];
                else //We need to build. And put it in the cache.
                {
                    objConstructInfo = new ObjectConstructionInfo(resultTable, collectionType);
                    map[tableNum] = objConstructInfo;   //cached now...
                }
            }
            else //Un-Cacheable. Database Server returns different number/structured tables based on query params!!
            {
                objConstructInfo = new ObjectConstructionInfo(resultTable, collectionType);
            }

            foreach (DataRow row in resultTable.Rows)
            {
                //Let's create the main object
                T result = (T)objConstructInfo.ConstructObject(typeof(T), row, resultTable.Columns);
                results.Add(result);
            }
            return results;
        }

        //Wrapper around the ^^ method; for which the results contain only one table
        private CollectionT GetCollection_FromTable<T, CollectionT>(DataTable resultTable)
            where CollectionT : Collection<T>, new()
            where T : new()
        {
            return GetCollection_FromTable<T, CollectionT>(resultTable, 0);
        }

        //To be used when multiple tables are returned in a dataset. 
        public CollectionT GetCollection_FromTable<T, CollectionT>(DataSet ds, int tableNum)
            where CollectionT : Collection<T>, new()
            where T : new()
        {
            return GetCollection_FromTable<T, CollectionT>(ds.Tables[tableNum], tableNum);
        }

        //To be used when multiple tables are returned in a dataset. 
        public T GetObject_FromTable<T>(DataSet ds, int tableNum)
            where T : new()
        {
            Collection<T> results = GetCollection_FromTable<T, Collection<T>>(ds.Tables[tableNum], tableNum);
            if (results.Count > 0) return results[0];
            else return default(T);
        }

        //To be used when multiple tables are returned in a dataset. 
        public List<T> GetList_FromTable<T>(DataSet ds, int tableNum)
            where T : new()
        {
            Collection<T> results = GetCollection_FromTable<T, Collection<T>>(ds.Tables[tableNum], tableNum);
            return new List<T>(results);
        }

        #region Gets Data from DB

        //Returns results as a Collection
        //Assumes only one table is returned.
        public CollectionT GetCollection<T, CollectionT>()
            where CollectionT : Collection<T>, new()
            where T : new()
        {
            DataTable resultTable = GetResultsAsTable();
            return GetCollection_FromTable<T, CollectionT>(resultTable);
        }

        //Returns results as a List
        //Assumes only one table is returned.
        public List<T> GetList<T>()
            where T : new()
        {
            return new List<T>(GetCollection<T, Collection<T>>());
        }

        //Returns results as an Object
        //Assumes only one table is returned.
        //Actually, takes the 1st row and discards the rest.
        public T GetObject<T>()
             where T : new()
        {
            Collection<T> results = GetCollection<T, Collection<T>>();
            if (results.Count > 0) return results[0];
            else return default(T);
        }

        #endregion

        #region Get da Command~!
        public static CreateCommand GetCreateCommand(string connectionString, DatabaseType databaseType)
        {
            switch (databaseType)
            {
                case DatabaseType.SqlServer:
                    return delegate(string commandText, CommandType commandType, bool resultStructureIsDynamic)
                    {
                        return new SqlDatabaseCommand(commandText, commandType, resultStructureIsDynamic, connectionString);
                    };
                default:
                    throw new NotImplementedException("Database not supported.");
            }
        }

        public static DatabaseCommand GetCommand(string commandText, CommandType commandType, bool resultStructureIsDynamic, string connectionString, DatabaseType databaseType)
        {
            switch (databaseType)
            {
                case DatabaseType.SqlServer:
                    return new SqlDatabaseCommand(commandText, commandType, resultStructureIsDynamic, connectionString);
                default:
                    throw new NotImplementedException("Database not supported.");
            }
        }

        #endregion
    }
}